# -*- coding: UTF-8 -*-
"""
Course:        CS408 Computer Science Project B, TEAM 1
Semester:      2010 SPRING
Project:       DOHN(Web-based Account Book System)

File:          transactions.py
Description:   Transaction package for transaction view.
Class:         TransactionView, TransactionController, Transaction

Author:        SungHyuk Im
"""


import os
import string
import xlrd

import sessions
import db
import utils
#import views


def __reassemble_items__(data, start = 0, end = 0, keyword = ""):
    """
    @author: SungHyuk Im
    @param start: start date of transaction list YY-MM-DD
    @param end: end date of transaction list YY-MM-DD
    @param keyword: keyword to search in transaction list
    @return: inner html source for transaction list.
    """
    check_range = not(0 == start and 0 == end)
    check_keyword = not("" == keyword)
    
    balance = 0.0
    tr_list = ["\t\t\t<tr>\n", "\t\t\t<tr class=\"odd\">\n"]
    in_src = []
    for i, atuple in enumerate(data):
        #in_src.append(tr_list[i % 2])
        if(0 > float(atuple[2])):
            in_src.append(tr_list[0])  # normal
        else:
            in_src.append(tr_list[1])  # odd
        
        balance += float(atuple[2])
        
        if check_range:
            if not(str(atuple[0])[:10] >= start and str(atuple[0])[:10] <= end):
                continue
        
        if check_keyword:
            if -1 == str(atuple).lower().find(str(keyword).lower()):
                continue
        
        for j, value in enumerate(atuple):
            aitem = str(value).strip()
            # Date
            if 0 == j:
                aitem = aitem[:10]  #ex: 2010-04-30
            # Description
            elif 1 == j:
                aitem = "<p align=\"left\">" + aitem + "</p>" 
            # Amount
            elif 2 == j:
                #balance += float(aitem)  # change location due to skip
                aitem = "<p align=\"right\">%s</p>" % \
                        (utils.get_split_float(aitem))
            # Category
            elif 3 == j:
                aitem = utils.get_category_name(aitem)
            # Num
            elif 4 == j:
                # Edit, Del Button
                del_src = "\t\t\t\t<td>" + \
                          "<a href=\"do_transaction_delete?num=%s\">" % (aitem) + \
                          "Del</a></td>\n"
                edit_src = "\t\t\t\t<td>" + \
                           "<a href='javascript:open_window(\"transaction_edit?num=%s\")'>Edit</a>\n" % (aitem)
                in_src.append(edit_src)
                in_src.append(del_src)
                break
            
            in_src.append("\t\t\t\t<td>%s</td>\n" % (aitem))
            # Balance
            if 2 == j:
                in_src.append("\t\t\t\t<td><p align=\"right\"><b>%s</b></p></td>\n" % 
                              (utils.get_split_float(balance)))
        #End for j
        in_src.append("\t\t\t</tr>\n")
    #End for i
    in_src = ''.join(in_src)
    if 1 >= len(in_src):
        in_src = str(in_src)
    return in_src


class TransactionView:
    """
    """
    def show_index_window(self, req):
        """
        @author: SungHyuk Im
        @summary: Index Window of Transaction
        @param req: Request 
        """
        date1 = utils.get_past_date()
        date2 = utils.get_current_date()
        return self.show_list_window(req, date1, date2)
    
    def show_list_window(self, req, start = 0, end = 0, keyword = ""):
        """
        @author: SungHyuk Im
        @param req: Request
        @return: HTML source of transaction list window 
        @see: 
        
        Information: trasaction_tb
            num  user_id  date  description  amount  category  deleted
        
        Information: Page layout(HTML)
            Date Description Amount Balance Category Modify            
            <thead>
                <tr>
                    <th scope="col">Date</th>
                    <th scope="col">Description</th>
                    <th scope="col">Amount</th>
                    <th scope="col">Balance</th>
                    <th scope="col">Category</th>
                    <th scope="col" colspan=2>Modify</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Date1</td>
                    <td>Description1</td>
                    <td>Amount1</td>
                    <td>Balance1</td>
                    <td>Category1</td>
                    <td style="width:5%">Edit</td>
                    <td style="width:5%">Del</td>
                </tr>
                <tr class="odd">
                    <td>Date1</td>
                    <td>Description1</td>
                    <td>Amount1</td>
                    <td>Balance1</td>
                    <td>Category1</td>
                    <td>Edit</td>
                    <td>Del</td>
                </tr>
                <!---CONTENT!--->
            </tbody>
        """
        
        # Fetch transaction list ----------------------------------------
        # @see: last item is num, it is index.
        session_id = sessions.get_id(req)
        query = "SELECT date, description, amount, category, num " + \
                "FROM transaction_tb " + \
                "WHERE(user_id = '%s' AND deleted = '0') " % (session_id) + \
                "ORDER BY date"  # ascending order
        data = db.run_query(query)
        
        # Reassemble items ----------------------------------------
        # @see: I use not string concatenation but list join for speed up.
        if 0 == start and 0 == end and "" == keyword:
            in_src = __reassemble_items__(data)
        elif not "" == keyword:
            in_src = __reassemble_items__(data, 0, 0, keyword)
        else:
            in_src = __reassemble_items__(data, start, end)
        
        # Get HTML and replace ----------------------------------------
        #src = utils.replace(src, "<!---TRX!--->", in_src)
        #src = utils.replace("transaction.html", in_src)
        trx_title = session_id + "'s Transaction List"
        src_path = src_path = utils.get_abs_path() + "/html/transaction.html"
        src = str(utils.get_file_content(src_path))
        src = src.replace("TransactionTopTitle", trx_title)
        src = src.replace("<!---CONTENT!--->", in_src)
        
        date1 = "%s\"/>" % start
        date2 = "%s\"/>" % end
        src = src.replace("<!---DATE1!--->", date1)
        src = src.replace("<!---DATE2!--->", date2)
        return src
    
    def show_add_window(self, req):
        """
        @author: SungHyuk Im
        @summary: Transaction Add Window.
        @return: HTML source of transaction add window
        @see: category list(select box) should be added in this page.
        
        Information: category_tb
            num  user_id  description  order
        """
        session_id = sessions.get_id(req)
        query = "SELECT num, description FROM category_tb " + \
                "WHERE(user_id = '%s') ORDER BY `order`" % (session_id)
        data = db.run_query(query)
        
        # Category
        in_src = str(utils.get_category_dropdown_src(session_id))
        
        src_path = utils.get_abs_path() + "/html/p_add_transaction.html"
        src = str(utils.get_file_content(src_path))
        src = src.replace("<!---CONTENT!--->", in_src)
        
        # Current date
        date = utils.get_current_date()
        src = src.replace("<!---DATE!--->", date)
        return src
    
    def show_edit_window(self, req, num):
        """
        @author: SungHyuk Im
        @see:
        Information: Content layout of edit transaction window
            <td>Content_Date</td>
            <td>Content_Description</td>
            <td>Content_Amount</td>
            <td>Content_Category</td>
        """
        # Fetch transaction data ----------------------------------------
        session_id = sessions.get_id(req)
        query = "SELECT date, description, amount, category, user_id " + \
                "FROM transaction_tb WHERE(num='%s')" % (num)
        data = db.run_query(query)
        if 0 == len(data):
            return "There is no transaction data on num=%s." % (num)
        
        # Check permission ----------------------------------------
        if not data[0][4] == sessions.get_id(req):
            return "You don't have permission to edit this transaction."
        
        # Unpack tuple of transaction ----------------------------------------
        date, desc, amnt, cate, user_id = data[0]
        date = str(date)[:10]
        cate_name = str(utils.get_category_name(cate))
        
        # Make category data ----------------------------------------
        in_src = str(utils.get_category_dropdown_src_with_selected(session_id, cate))
            
        # Make hidden input box for num ----------------------------------------
        hidden_input = "<input type=\"hidden\" name=\"num\" " + \
                       "value=\"%s\" />" % (num)
        
        # Get HTML and replace ----------------------------------------
        src_path = utils.get_abs_path() + "/html/p_edit_transaction.html"
        src = str(utils.get_file_content(src_path))
        
        src = src.replace("Content_Date", str(date))
        src = src.replace("Content_Description", desc)
        src = src.replace("Content_Amount", str(amnt))
        src = src.replace("Content_Category", cate_name)
        src = src.replace("<!---CONTENT!--->", in_src)
        src = src.replace("<!---HIDDEN!--->", hidden_input);
        return src
    
    def show_import_window(self, req):
        """
        @author: SungHyuk Im
        """
        src_path = utils.get_abs_path() + "/html/import.html"
        src = utils.get_file_content(src_path)
        return src
    
    def show_suggest_window(self, req, temp_name):
        """
        @author: SungHyuk Im
        """
        trx_obj = TransactionController()
        return trx_obj.process_import(req, temp_name)


class TransactionController:
    """
    @author: SungHyuk Im
    """
    def query_add_transaction(self, req, date, description, amount, category):
        """
        @author: SungHyuk Im
        @summary: Execute two INSERT queries to save transaction.
        @param date: string, represents date(format: YY-MM-DD)
        @param description
        @param amount
        @param category: category num or name, 
            it should be converted into num.
        @see:
        Information: transaction_tb
            num  user_id  date  description  amount  category  deleted
        """
        user_id = sessions.get_id(req)
        if not utils.is_number(category):
            category = utils.get_category_num(user_id, category)
        
        # user_id, date, description, amount, category
        # ex: ('test', '2010-05-01', 'asdf', '100000', '1')
        # transaction_tb
        query = "INSERT INTO transaction_tb (user_id, date, description, " + \
                "amount, category, deleted) VALUES " + \
                "('%s', '%s 00:00:00', '%s', '%+15.2f', '%s', '0')" \
                % (user_id, date, description, float(amount), int(category))
        db.execute_query(query)
        
        # pattern_tb
        query = "SELECT num FROM pattern_tb WHERE (user_id='%s' AND category='%d' " % (user_id, int(category)) + \
                "AND description='%s')" % (description)
        data = db.run_query(query)
        if len(list(data)) == 0:
                query = "INSERT INTO pattern_tb (user_id, category, description) " + \
                "VALUES('%s', '%s', '%s')" % (user_id, category, description)
                db.execute_query(query)
                
        return
    
    def query_edit_transaction(self,
                               req, num, date, description, amount, category):
        """
        @author: SungHyuk Im
        @summary: Update transaction to given data.
        @param num: transaction index
        """
        # example query
        """
UPDATE `dohn`.`transaction_tb` SET `description` = 'trx2 description!' 
WHERE `transaction_tb`.`num` =3 AND `transaction_tb`.`user_id` = 'test' AND
      `transaction_tb`.`category` =2 LIMIT 1 ;
        """
        user_id = sessions.get_id(req)
        if not utils.is_number(category):
            category = utils.get_category_num(user_id, category)
        
        # past data
        query = "SELECT description, category FROM transaction_tb " + \
                "WHERE( user_id='%s' AND num='%s')" % (user_id, num)
        data = db.run_query(query)  #: (('trx1', 0L),)
        past_description = str(data[0][0]).strip()
        past_category = str(data[0][1]).strip()
        
        # transaction_tb
        query = "UPDATE transaction_tb SET " + \
                "date = '%s 00:00:00'" % (date) + \
                ", description = '%s', " % (description) + \
                "amount = '%+15.2f', " % (float(amount)) + \
                "category = '%s' " % (category) + \
                "WHERE(user_id='%s' AND num = '%s') LIMIT 1" % (user_id, num)
        db.execute_query(query)
        
        # pattern_tb
        query = "SELECT num FROM pattern_tb WHERE (user_id='%s' AND category='%d' " % (user_id, int(category)) + \
                "AND description='%s')" % (description)
        data = db.run_query(query)
        if len(list(data)) == 0:
            query = "UPDATE pattern_tb SET category = '%s', " % (category) + \
                "description = '%s' " % (description) + \
                "WHERE(user_id='%s' AND description='%s' AND category='%s')" \
                % (user_id, past_description, past_category)
            db.execute_query(query)
        return
    
    def query_delete_transaction(self, req, num):
        """
        @author: SungHyuk Im
        @summary: Delete a given transaction. It changes deleted to 1.
        @param num: transaction index
        """
        user_id = sessions.get_id(req)
        
        # Check permission --------------------
        query = "SELECT user_id FROM transaction_tb " + \
                "WHERE(num='%s')" % (num)
        data = db.run_query(query)
        if not data[0][0] == user_id:
            return "You don't have permission to edit this transaction."
        
        # past data --------------------
        query = "SELECT description, category FROM transaction_tb " + \
                "WHERE(user_id='%s' AND num='%s')" % (user_id, num)
        data = db.run_query(query)
        past_description = str(data[0][0]).strip()
        past_category = int(data[0][1])
        
        # transaction_tb --------------------
        query = "UPDATE transaction_tb SET deleted = '1' " + \
                "WHERE(user_id='%s' AND num = '%s') LIMIT 1" % (user_id, num)
        db.execute_query(query)
        
        # count existing desc-category
        query = "SELECT description, category FROM transaction_tb " + \
                "WHERE(user_id='%s' AND description='%s' AND category='%s')" % (user_id, past_description, past_category )
        data = db.run_query(query)
        cnt = len(data)
        
        if(cnt == 1):  # if deleting transaction is last one.
            # pattern_tb --------------------
            query = "DELETE FROM pattern_tb WHERE(user_id='%s' " % (user_id) + \
                    "AND description='%s' " % (past_description) + \
                    ") LIMIT 1"
            db.execute_query(query)
        return True
    
    def process_import(self, req, file_path):
        """
        @author: SungHyuk Im
        @summary: Main import handler. It calls process_file and process_suggestion.
        @param file_path: Target file path saved from dohn.py/do_import_file
        
        @see:
        Information: Content layout of suggest window
                <tr>
                    <td>
                        {$date}
                    </td>
                    <td>
                        {$amount}
                    </td>
                    <td>
                        {$Description}
                    </td>
                    <td>
                        <form>
                        <select name="Category">
                        <option value="{$Category}">{$Category}</option>
<!---CATEGORY!--->
                        </select>
                        </form>
                    </td>
                    <td> -> </td>
                    <td>{$Suggestion}</td>
                    <td><input type="checkbox" name="ch" value="Del">Del</td>
                </tr>
        """
        # date, desc, amnt are lists.
        ret_val = self.process_file(file_path)
        if not isinstance(ret_val, tuple):    # if file open error,
            return ret_val
        date, desc, amnt = self.process_file(file_path)
        
        #return self.process_suggestion(req, date, desc, amnt)
        
        suggest = self.process_suggestion(req, date, desc, amnt)
        # now we have, date, desc, amnt, suggest / 4 lists
        
        user_id = sessions.get_id(req)
        pa_cate = []
        pa_desc = []
        data = utils.get_all_pattern_data(user_id)
        # SELECT num, category, description
        if len(data) > 0:
            for atuple in data:
                pa_cate.append(str(atuple[1]))
                pa_desc.append(str(atuple[2]))
        else:
            pa_cate = []
            pa_desc = []
        for i, aitem in enumerate(pa_desc):
            #EX) u = unicode(a, 'euc-kr').encode('utf-8')
            #pa_desc[i] = aitem.decode('utf-8')
            pa_desc[i] = unicode(aitem, 'utf-8').encode('euc-kr')
        
        in_src = []
        for i in range(len(date)):
            #return desc[i]                     - original
            #desc[i] = desc[i].encode('euc-kr') - use
            #desc[i] = desc[i].decode('euc-kr') - restore
            #desc[i] = desc[i].encode('utf-8')  -   ''
            #return str(desc[i])                - original
            
            aitem = "<tr>\n"
            aitem += "\t<td><input type=\"text\" name=\"date\"        value=\"%s\" style=\"width:80px;\" /></td>\n" % (date[i])
            aitem += "\t<td><input type=\"text\" name=\"description\" value=\"%s\" style=\"width:120px;\" /></td>\n" % (desc[i])
            aitem += "\t<td><input type=\"text\" name=\"amount\"      value=\"%s\" style=\"width:70px;\" /></td>\n" % (amnt[i])
            
            category_src = ""
            found = -1
            try:  # if description is in pattern!
                desc[i] = desc[i].encode('euc-kr')
                found = pa_desc.index(desc[i])
                desc[i] = desc[i].decode('euc-kr')
                desc[i] = desc[i].encode('utf-8')
                #return found, desc[i], pa_desc[found], pa_cate[found]
            except:
                category_src = utils.get_category_dropdown_src(user_id)
            else:
                #return found, desc[i], pa_desc[found], pa_cate[found]
                category_src = utils.get_category_dropdown_src_with_selected(user_id, pa_cate[found])
                #return category_src
            
            aitem += "\t<td><select name=\"category\">\n" + \
                     "\t<option value="">Select Category...</option>\n" + \
                     "%s</select></td>\n" % (category_src)
            aitem += "\t<td>-></td>\n"
            aitem += "\t<td><img src=\"../html/images/%d.png\" title=\"hi\"></td>\n" % (suggest[i])
            if suggest[i] == 1:
                aitem += "\t<td><input type=\"checkbox\" name=\"deleted\" value=\"%s\" checked >Del</td>\n" % (i)
            else:
                aitem += "\t<td><input type=\"checkbox\" name=\"deleted\" value=\"%s\">Del</td>\n" % (i)
            aitem += "</tr>\n" 
            in_src.append(aitem)
        
        in_src = ''.join(in_src)
        
        src_path = utils.get_abs_path() + "/html/import_2.html"
        src = utils.get_file_content(src_path)
        src = str(src).replace("<!---CONTENT!--->", in_src)
        return src
    
    def process_file(self, file_path):
        """
        @author: SungHyuk Im
        @summary: Using xlrd library, read excel file data.
        @return: Lists of date, description and amount
        
        @see: https://secure.simplistix.co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html
        """
        # @see
        # Before open_workbook,
        # Target file should be saved as Excel 97-2003 format, xls.
        
        # Inner functions------------------------------
        def check_bank():
            return 'woori'
        
        def read_woori_bank():
            # @@information: Format of exported WooriBank xls file
            # Total: # rows, 9 columns
            # Start: B7 / row: 6, col: 1 / Date
            # Finish: H# / row: #-1, col: 7 / Memo
            #
            # Date: col 1
            # Description: col 3
            # Amount: + col 4 / - col 5
            
            #print sh.nrows, sh.ncols
            #print sh.cell(6, 4)
            #print sh.cell_value(6, 4)
            
            date = []
            desc = []
            amnt = []
            for i in range(6, sh.nrows):
                date.append(sh.cell_value(i, 1))
                desc.append(sh.cell_value(i, 3))
                amnt.append(sh.cell_value(i, 5) - sh.cell_value(i, 4))
            return date, desc, amnt
            
        def read_epost_bank():
            return [], [], []
        
        # Process ------------------------------
        try:
            book = xlrd.open_workbook(file_path)
        except:
            """
            error_str = "<html><body>" + \
                        "<h3>ERROR: Supported file types are following</h3>\n" + \
                        "<br>1. Woori Bank Transaction List File\n" + \
                        "<br>2. Epost Bank Transaction List File\n\n\n<br><br><br>" + \
                        "Please check your file and try again.\n" + \
                        "</body>\n</html>"
            return error_str
            """
            src_path = utils.get_abs_path() + "/html/import_error.html"
            src = utils.get_file_content(src_path)
            return src
            
        sh = book.sheet_by_index(0)
        
        if check_bank() == 'woori':
            return read_woori_bank()
        else:
            return read_epost_bank()
    
    def process_suggestion(self, req, file_date, file_desc_arg, file_amnt):
        """
        @author: SungHyuk Im
        @summary: Import process method.
        @version: 2.0
        @param req: request
        @param file_date: List of date from imported file
        @param file_desc_arg: List of description from imported file
        @param file_amnt: List of amount from imported file
        @return: List of Suggestion type
        @note:
        ver 1.0
            First version is finalized.
            Categorizing suggestion by pointing using state binary tree.
            To-do 1: Actually it only cares a fist transaction matched
        Ver 2.0
            Suggestion within all transaction list is implemented.
            To-do 1 is fixed.
            To-do 2: text matching?
        Ver 3.0
            If transaction DB
        @see:
        Suggestion criteria and state binary tree are set by SungHyuk Im.
        """
        user_id = sessions.get_id(req)
        data = utils.get_all_transaction_data(user_id)
        tr_date = []
        tr_desc = []
        tr_amnt = []
        tr_cate = []
        file_desc = []
        
        # SELECT num, date, description, amount, category, deleted 
        if len(data) > 0:
            for atuple in data:
                if atuple[5] != '0':
                    tr_date.append(str(atuple[1])[:10])
                    tr_desc.append(unicode(atuple[2], 'utf-8').encode('euc-kr'))
                    tr_amnt.append(str(atuple[3]))
                    tr_cate.append(str(atuple[4]))
        
        result_def = [4, 4, 4, 3 ,4 ,2, 3, 1]  # Point to suggest type
        result = []  # Result of sugestion(it will be returned)
        number = len(file_date)
        for i in range(number):
            file_amnt[i] = str(file_amnt[i])
            file_desc.append("")
            file_desc[i] = file_desc_arg[i].encode('euc-kr')
            
            # date: 1
            # desc: 2
            # amnt: 4
            point = 0
            
            #return file_date[i], tr_date
            # I made state binary tree for deciding suggestion.
            
            set_amnt = []
            cnt = 0
            #cnt = tr_amnt.count(file_amnt[i])
            for j in range(len(tr_amnt)):
                if file_amnt[i] == tr_amnt[j]:
                    cnt += 1
                    set_amnt.append(j)
            
            # Pointing -----------------------------------
            # AMNT o
            if cnt > 0:
                point += 4
                set_desc = []
                cnt = 0
                for j in set_amnt:
                    if file_desc[i] == tr_desc[j]:
                        cnt += 1
                        set_desc.append(j)
                
                # DESC o
                if cnt > 0:
                    point += 2
                    # DATE
                    #return file_date[i], tr_date[found_desc]
                    for j in set_desc:
                        if file_date[i] == tr_date[j]:
                            point += 1
                            break
                        
                # DESC x
                else:
                    # DATE
                    for j in range(len(set_amnt)):
                        if file_date[i] == tr_date[j]:
                            point += 1
                            break

            # AMNT x
            else:
                set_desc = []
                cnt = 0
                for j in range(len(tr_desc)):
                    if file_desc[i] == tr_desc[j]:
                        cnt += 1
                        set_desc.append(j)
                
                # DESC o
                if cnt > 0:
                    point += 2
                    # DATE
                    for j in set_desc:
                        if file_date[i] == tr_date[j]:
                            point += 1
                            break

                # DESC x
                else:
                    # anyway this section get 0 point, 1 point --> suggestion 4
                    pass
            # -----------------------------------
            
            #result.append(point)
            result.append(result_def[point])
            
            file_desc[i] = file_desc[i].decode('euc-kr')
            file_desc[i] = file_desc[i].encode('utf-8')
        #### END FOR
        return result

class Transaction:
    """
    """
    def __init__(self, description = "", amount = 0.0, category = 1, \
                  date = utils.get_current_date()):
        """
        """
        self.description = description
        self.amount = amount
        self.category = category
        self.date = date
